

/*
Filename: B1_MeetingDetails.do
Goal: Construct a comprehensive database capturing shareholder meeting characteristics, with particular focus on identifying meetings with contentious proposals.
Contact: mjha@gsu.edu (author) and gormley@wustl.edu

Data Sources
1. Voting Data (Proprietary)
File: VoteResults.dta
Source Path: WRDS → ISS ESG → Voting Analytics
Access: Requires WRDS subscription
Description: Aggregated voting outcomes and proposal details for shareholder meetings
Purpose: Identify contentious proposals through voting patterns, margins, and opposition levels

2. Company Identifier Links (Proprietary)
File: CIK_CUSIP_wrds.dta
Source Path: WRDS → SEC Analytics Suite by WRDS → SEC Linking Tables → CIK-CUSIP Link Table
Access: Requires WRDS subscription
Description: Links SEC CIK (Central Index Key) identifiers to CUSIP security identifiers
Purpose: Connect voting data to SEC filings and company information

3. Additional CIK-CUSIP Links (Public)
File: CIK_CUSIP.dta
Source: GitHub repository: cik-cusip-mapping
Access: Publicly available
Description: Supplementary CIK-CUSIP mappings derived from SEC filings
Purpose: Fill gaps in WRDS linking table and expand coverage
*/



use "VoteResults_pseudo.dta", clear 

drop if missing(cusip) | missing(meetingdate)
gen cusip6 = substr(cusip,1,6)
gen day = day(meetingdate)
gen month = month(meetingdate)
gen year = year(meetingdate)

drop if year > 2017
drop if year == 2017 & month > 6 

// unique index
gsort itemonagendaid meetingdate 
quietly by itemonagendaid meetingdate:  gen dup = cond(_N==1,0,_n)
count if dup > 1
drop if dup > 1 
drop dup
egen meetunq = group(itemonagendaid meetingdate)

// add cik 
joinby cusip6 using "CIK_CUSIP_wrds_pseudo.dta", unmatched(master)

// remove bad combinations
replace tmatch = 0 if (meetingdate < CIKDATE1 - 30 | meetingdate > CIKDATE2 + 30) 
gsort meetunq -tmatch tmatchrank 
quietly by meetunq:  gen dup = cond(_N==1,0,_n)
drop if dup > 1
drop CIKDATE1 CIKDATE2 tmatch tmatchrank cusip dup _merge 

rename cik cik_temp
merge m:1 cusip6 using "CIK_CUSIP_pseudo.dta" 
drop if _merge == 2
drop _merge 
replace cik_temp = cik if missing(cik_temp)
drop cik

rename cik_temp cik
drop if missing(cik) 
save "meeting_withcik.dta", replace 


****************************
* capture meeting details
use "meeting_withcik.dta", clear 
gsort cik meetingdate
quietly by cik meetingdate:  gen dup = cond(_N==1,0,_n)

gen tempsp = 0
replace tempsp = 1 if sponsor == "Shareholder"
egen SP = mean(tempsp), by(cik meetingdate)

********************* Contentious i.e., ISS recommends against management 
gen contentious 	= 0
replace contentious = 1 if (mgmtrec=="Abstain"|mgmtrec=="Do Not Vote"|mgmtrec=="Withhold"|mgmtrec=="Against") & (issrec=="For")
replace contentious = 1 if (mgmtrec=="For") & (issrec=="Abstain"|issrec=="Do Not Vote"|issrec=="Withhold"|issrec=="Against")
replace contentious = 1 if mgmtrec=="One Year" & (strpos(issrec, "Year") & !strpos(issrec, "One"))
replace contentious = 1 if mgmtrec=="Two Years" & (strpos(issrec, "Year") & !strpos(issrec, "Two"))
replace contentious = 1 if mgmtrec=="Three Years" & (strpos(issrec, "Year") & !strpos(issrec, "Three"))
egen CS = mean(contentious), by(cik meetingdate)

gen tempspcs = 0
replace tempspcs = 1 if tempsp*contentious == 1
egen SPCS = mean(tempspcs), by(cik meetingdate)

/* keep unique */
drop if dup > 1 
keep cik meetingdate SP CS SPCS meetunq 
save "MeetingDetails.dta", replace


***************************************** meeting say on pay
use "meeting_withcik.dta", clear 
gsort cik meetingdate
quietly by cik meetingdate:  gen dup = cond(_N==1,0,_n)

*** check sayonpay
rename agendageneraldesc temp
gen agendageneraldesc = lower(temp)
drop temp

rename agendageneraldesc temp
gen agendageneraldesc = lower(temp)
drop temp

gen sp = 0
replace sp = 1 if inlist(agendageneraldesc, ///
    "advisory vote on say on pay frequency", ///
	"bundled say on pay/golden parachute advisory vote", ///
	"advisory vote on executive compensation approach", ///
    "advisory vote to ratify named executive officers'compensation", ///
    "advisory vote on remuneration report", ///
    "approve remuneration report (advisory vote)", ///
    "bundled say on pay/golden parachute advisory vote", ///
    "advisory vote to ratify named executive officers' compensation", ///
    "advisory vote on golden parachutes") //31k

egen saypay = mean(sp), by(cik meetingdate)

drop if dup > 1 
keep cik meetingdate saypay agendageneraldesc
save "MeetingDetails_saypay.dta", replace 
